/*
 * 软件版权: 恒生电子股份有限公司
 * 修改记录:
 * 修改日期     修改人员  修改说明
 * ========    =======  ============================================
 * 2021/4/13  lisy31662  新增
 * ========    =======  ============================================
 */
package com.sansi.pencilbook.mapper;

import com.sansi.pencilbook.entity.RelationBookDTO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.sql.SQLException;
import java.util.List;

/**
 * 功能说明:
 *
 * @author lisy31662
 */
@Mapper
public interface RelationBookDTOMapper {

  @Select("select ubr.*, sb.number, sb.img, sb.price, c.category_name, b.book_name, book_publishers, " +
      "book_price, book_author, book_introduction, book_time " +
      "from user_book_relation ubr " +
      "left join store_book sb on sb.store_id=ubr.store_id and sb.book_isbn=ubr.book_isbn " +
      "left join book b on b.book_isbn=ubr.book_isbn " +
      "left join category c on c.category_id=b.category_id ")
  public List<RelationBookDTO> findRelationBookDTOAll() throws SQLException;

  @Select("select ubr.*, sb.number, sb.img, sb.price, c.category_name, b.book_name, book_publishers, " +
      "book_price, book_author, book_introduction, book_time " +
      "from user_book_relation ubr " +
      "left join store_book sb on sb.store_id=ubr.store_id and sb.book_isbn=ubr.book_isbn " +
      "left join book b on b.book_isbn=ubr.book_isbn " +
      "left join category c on c.category_id=b.category_id " +
      "where ubr.user_id=#{userId}")
  public List<RelationBookDTO> findRelationBookDTOByUser(int userId) throws SQLException;

  @Select("select ubr.*, sb.number, sb.img, sb.price, c.category_name, b.book_name, book_publishers, " +
      "book_price, book_author, book_introduction, book_time " +
      "from user_book_relation ubr " +
      "left join store_book sb on sb.store_id=ubr.store_id and sb.book_isbn=ubr.book_isbn " +
      "left join book b on b.book_isbn=ubr.book_isbn " +
      "left join category c on c.category_id=b.category_id " +
      "where ubr.user_id=#{userId} and ubr.relation=#{relation}")
  public List<RelationBookDTO> findRelationBookDTOByUserRelation(int userId, String relation) throws SQLException;

  @Select("select ubr.*, sb.number, sb.img, sb.price, c.category_name, b.book_name, book_publishers, " +
      "book_price, book_author, book_introduction, book_time " +
      "from user_book_relation ubr " +
      "left join store_book sb on sb.store_id=ubr.store_id and sb.book_isbn=ubr.book_isbn " +
      "left join book b on b.book_isbn=ubr.book_isbn " +
      "left join category c on c.category_id=b.category_id " +
      "where ubr.user_id=#{userId} and ubr.store_id=#{storeId} and ubr.book_isbn=#{bookIsbn}")
  public RelationBookDTO findRelationBookDTOOne(int userId, int storeId, String bookIsbn) throws SQLException;

}

